﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.IO;

namespace EmaillSend
{
    public class CommonDBUtils
    {
        /// <summary>
        /// 连接数据库字符串
        /// </summary>
        private string connectionString; 

        /// <summary>
        /// 存储数据库连接（保护类，只有由它派生的类才能访问）
        /// </summary>
        protected OleDbConnection Connection; 


        /// <summary>
        /// 构造函数：数据库的默认连接
        /// </summary>
        public CommonDBUtils()
        {
            connectionString = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + Directory.GetCurrentDirectory() + (@"\MailDB.accdb");
            Connection = new OleDbConnection(connectionString);
        } 

        /// <summary>
        /// 构造函数：带有参数的数据库连接
        /// </summary>
        /// <param name="newConnectionString"></param>
        public CommonDBUtils(string newConnectionString)
        {
            //connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + newConnectionString;
            connectionString = newConnectionString;
            Connection = new OleDbConnection(connectionString);
        } 

        /// <summary>
        /// 获得连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return connectionString;
            }
        } 

        /// <summary>
        /// 执行SQL语句没有返回结果，如：执行删除、更新、插入等操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>操作成功标志</returns>
        public bool ExeSQL(string strSQL)
        {
            bool resultState = false; 

            Connection.Open();
            OleDbTransaction myTrans = Connection.BeginTransaction();
            OleDbCommand command = new OleDbCommand(strSQL, Connection, myTrans); 
            
            try
            {
                command.ExecuteNonQuery();
                myTrans.Commit();
                resultState = true;
            }
            catch(Exception e)
            {                
                myTrans.Rollback();
                resultState = false;
            }
            finally
            {
                Connection.Close();
            }
            return resultState;
        }

        /// <summary>
        /// 执行SQL语句没有返回结果，如：执行删除、更新、插入等操作
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>操作成功标志</returns>
        public bool ExeBatchSQL(List<string> strSQL)
        {
            bool resultState = false;
            if(strSQL == null || strSQL.Count == 0){
                return true;
            }
            Connection.Open();
            OleDbTransaction myTrans = Connection.BeginTransaction();
            OleDbCommand command = new OleDbCommand();

            try
            {
                command.Connection = Connection;
                command.Transaction = myTrans;
                foreach (string sql in strSQL)
                {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
                
                myTrans.Commit();
                resultState = true;
            }
            catch (Exception e)
            {
                myTrans.Rollback();
                resultState = false;
            }
            finally
            {
                Connection.Close();
            }
            return resultState;
        } 

        /// <summary>
        /// 执行SQL语句返回结果到DataReader中
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>dataReader</returns>
        private OleDbDataReader ReturnDataReader(string strSQL)
        {
            Connection.Open();
            OleDbCommand command = new OleDbCommand(strSQL, Connection);
            OleDbDataReader dataReader = command.ExecuteReader();
            Connection.Close(); 

            return dataReader;
        } 

        /// <summary>
        /// 执行SQL语句返回结果到DataSet中
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>DataSet</returns>
        public DataSet ReturnDataSet(string strSQL)
        {
            Connection.Open();
            DataSet dataSet = new DataSet();
            OleDbDataAdapter OleDbDA = new OleDbDataAdapter(strSQL, Connection);
            OleDbDA.Fill(dataSet); 

            Connection.Close();
            return dataSet;
        } 

        /// <summary>
        /// 执行一查询语句，同时返回查询结果数目
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns>sqlResultCount</returns>
        public int ReturnSqlResultCount(string strSQL)
        {
            int sqlResultCount = 0; 

            try
            {
                Connection.Open();
                OleDbCommand command = new OleDbCommand(strSQL, Connection);
                OleDbDataReader dataReader = command.ExecuteReader(); 

                while (dataReader.Read())
                {
                    sqlResultCount++;
                }
                dataReader.Close();
            }
            catch
            {
                sqlResultCount = 0;
            }
            finally
            {
                Connection.Close();
            }
            return sqlResultCount;
        }

        public bool SaveBinaryData(string title, string subject, byte[] content, string id, string imgpath)
        {
            bool resultState = false; 
            Connection.Open();
            OleDbTransaction myTrans = Connection.BeginTransaction();

            try
            {
                OleDbCommand command = null;
                if (id != null && id != "")
                {
                    command = new OleDbCommand("update MailContent set Title=@Title, Subject=@Subject,Content=@Content,ImgPath=@ImgPath where ID=" + id, Connection, myTrans);
                }
                else
                {
                    command = new OleDbCommand("insert into MailContent(Title,Subject,Content,ImgPath) values(@Title,@Subject,@Content,@ImgPath)", Connection, myTrans);
                }
                command.Parameters.AddWithValue("@Title", title);
                command.Parameters.AddWithValue("@Subject", subject);
                command.Parameters.AddWithValue("@Content", content);
                command.Parameters.AddWithValue("@ImgPat", imgpath);

                command.ExecuteNonQuery();
                myTrans.Commit();
                resultState = true;
            }
            catch (Exception e)
            {
                myTrans.Rollback();
                resultState = false;
            }
            finally
            {
                Connection.Close();
            }
            return resultState;
        }  

        public DataSet importExcelToDataSet(string FilePath)  
        {
            string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;'";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {                
                conn.Open();
                //DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [Sheet1$] ", conn);
                DataSet myDataSet = new DataSet();
                myCommand.Fill(myDataSet);
                return myDataSet;
            }
            catch (Exception ex)
            {
                throw new Exception("该Excel文件的工作表的名字不正确," + ex.Message);
            }
            finally
            {
                conn.Close();
            }
           
        } 
    }

    public class Utils
    {
        public static bool isNotBlank(string str)
        {
            if (str == null) return false;
            if (str.Trim() == "")
                return false;
            else
                return true;
        }

        public static bool isBlank(string str)
        {
            if (str == null) return true;
            if (str.Trim() == "")
                return true;
            else
                return false;
        }

        public static string Basic64Ecode(string strText)
        {
            byte[] result = System.Text.UTF8Encoding.Default.GetBytes(strText);
            return Convert.ToBase64String(result); 
        }

        public static string Basic64Dcode(string strText)
        {
            byte[] result = Convert.FromBase64String(strText);
            return System.Text.UTF8Encoding.Default.GetString(result);
        }
        
    }
}
